/*
 * SaleDAO.java
 *
 * Created on March 31, 2007, 4:20 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.jjenkins.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * dAO for Sale
 * @author Rick
 */
public class SaleDAO extends RevenueSourceDAO{
    
    /**
     * SaleDAO.java
     * Created on March 28, 2007, 3:39 PM
     *
     *
     */
    
    private static SaleDAO instance = null;
    
    /** Creates a new instance of SaleDAO */
    public SaleDAO() {
    }
    
    
    
    /**
     * This method implements the singleton pattern. It insures that only
     * on instance of InterestDAO exists
     * @return: InterestDAO instance
     * @return instance
     */
    
    public static synchronized SaleDAO getInstance() {
        if (instance == null) {
            instance = new SaleDAO();
        }
        return instance;
    }
    
    
    /**
     * This method creates a new RevenueSourceBO with a unique QUID, puts the interest
     * in cache, and then returns it to the calling method.
     * @return: RevenueSourceBO
     * @throws: DataException
     * @throws edu.byu.isys413.jjenkins.data.DataException format
     * @return SAleBO
     */
    
    public RevenueSourceBO create() throws DataException {
        try {
            
            String id = GUID.generate();
            SaleBO object = new SaleBO();
            object.setId(id);
            Cache.getInstance().put(object.getId(), object);
            object.setType("sale");
            return object;
        } catch (Exception e) {
            throw new DataException("Could not create GUID", e);
        }
        
    }
    
    
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     * @param: String: id
     * @return: RevenueSourceBO
     * @throws: DataException
     * @param id id to read
     * @throws edu.byu.isys413.jjenkins.data.DataException format
     * @return BO
     */
    public synchronized RevenueSourceBO read(String id) throws DataException {
        
        // Checks to see if the item is in cache
        SaleBO object = null;
//        Cache cache = Cache.getInstance();
//        if(cache.containsKey(id)){
//            object = (SaleBO)cache.get(id);
//        }else{
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // If the item is not in cache a connection is checked out and
            // The item is read from the database.
            
            try {
                //reads in object
                object = (SaleBO)read(id, conn);
                
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve record for id=" + id, e);
            }
            
            
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        //}//elsenotcache
        // Return object
        return object;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  @param: String: id, Connection conn
     *  @return: RevenueSouceBO
     *  @throws: DataException, SQLException
     */
    synchronized RevenueSourceBO read(String id, Connection conn) throws SQLException, DataException {
        SaleBO object = null;
        //System.out.println("id is ins saleDAOright before read "+id);
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        if(cache.containsKey(id)){
            object = (SaleBO)cache.get(id);
        }else{
            // Quieries that database
            if(object == null){
                
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM sale WHERE revenue_source_id = ?");
                pstmt.setString(1, id);
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                if (rs.next()) {
                    object = new SaleBO();
                    object.setId(rs.getString("revenue_source_id"));
                    object.setQuantity(rs.getDouble("quantity"));
                    object.setProd_id(rs.getString("product_id"));
                    
                    
                    // Sets already in database
                    object.setObjectAlreadyInDB(true);
                    
                    // adds to cache
                    cache.put(object.getId(), object);
                    
                    // Reads in RevenueSource details
                    super.populateRevenueSource(object, conn);
                    
                }else{
                    throw new DataException("Object was not found in the database.");
                }
                
            }
            
        }//else contains
        // returns object
        return object;
    }
    
    
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     * @param object object to save
     * @throws edu.byu.isys413.jjenkins.data.DataException format
     */
    public synchronized void save(RevenueSourceBO object) throws DataException {
        try {
            
            
            // retrieve a database connection from the pool
            ConnectionPool connPool = ConnectionPool.getInstance();
            
            Connection conn = connPool.get();
            
            
            try {
                
                // Saves Interest
                save(object, conn);
                
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not save record for " + object.getId(), e);
            }
            
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            // touch the cache for the object
            Cache.getInstance().touch(object.getId());
            
            
            
        } catch (DataException e) {
            throw new DataException("Could not save record for" + object.getId(), e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not save record for" + object.getId(), e);
        } catch (SQLException e) {
            throw new DataException("Could not save record for" + object.getId(), e);
        }
        
        
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     *
     *  @param: RevenueSourceBO, Connection
     *  @throws: SQLException, DataException
     */
    synchronized void save(RevenueSourceBO object, Connection conn) throws SQLException, DataException {
        
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (object.isDirty()) {
            if (object.isObjectAlreadyInDB()) {
                super.update(object, conn);
                update(object, conn);
                
            }else{
                super.insert(object, conn);
                this.insert((SaleBO)object, conn);
            }
            object.setDirty(false);
        }
        
    }
    
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par SaleBO object
     * @param object
     * @param conn
     * @throws java.sql.SQLException
     * @throws edu.byu.isys413.jjenkins.data.DataException
     */
    protected synchronized void update(SaleBO object, Connection conn) throws SQLException, DataException {
        //i changed the update and insert to accept a saleBO object, because i couldnt get Prod_id any other way....
        //I dont know if correct, is this the right thing to do? - rick
        PreparedStatement pstmt = conn.prepareStatement("UPDATE sale SET \"quantity\" = ?, \"product_id\" = ? WHERE revenue_source_id = ?");
        pstmt.setString(1, object.getId());
        pstmt.setDouble(2, object.getQuantity());
        pstmt.setString(3, object.getProd_id());
        pstmt.execute();
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par SaleBO object
     * @param object
     * @param conn
     * @throws java.sql.SQLException
     * @throws edu.byu.isys413.jjenkins.data.DataException
     */
    protected synchronized void insert(SaleBO object, Connection conn) throws SQLException, DataException {
        //i changed the update and insert to accept a saleBO object, because i couldnt get Prod_id any other way....
        //I dont know if correct, is this the right thing to do? - rick
        // Creates prepared statemetn
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO sale VALUES(?, ?, ?)");
        pstmt.setString(1, object.getId());
        pstmt.setDouble(2, object.getQuantity());
        pstmt.setString(3, object.getProd_id());
        
        //Executes prepated statement
        pstmt.execute();
        
        //Sets already in database true
        object.setObjectAlreadyInDB(true);
        
    }
    
    /* We intentionally do not allow a SaleBO object to be deleted because
     * it could mess up relationships in the database. Obmitting the delete
     * also prevents fraud. */
    
    /**
     * this is not supported
     * @param object
     * @throws edu.byu.isys413.jjenkins.data.DataException
     */
    public synchronized void delete(RevenueSourceBO object)throws DataException{
    /* We intentionally do not allow a SaleBO object to be deleted because
     * it could mess up relationships in the database. Obmitting the delete
     * also prevents fraud. */
        throw new DataException("You can not delete a SaleBO");
    }
    
    double getProductPrice(String forSaleID) throws DataException {
        double price = readProductPrice(forSaleID);
        return price;
    }
    
    public double readProductPrice(String id) throws DataException {
        
        // Checks to see if the item is in cache
        double object = 0;
        Cache cache = Cache.getInstance();
//        if(cache.containsKey(id)){
//            object = (SaleBO)cache.get(id);
//        }else{
            try {
                
                // Gets connection
                ConnectionPool connPool = ConnectionPool.getInstance();
                Connection conn = connPool.get();
                
                // If the item is not in cache a connection is checked out and
                // The item is read from the database.
                
                try {
                    //reads in object
                    object = readProductPrice(id, conn);
                    
                }catch (SQLException e) {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
                }
                
                
                // connection committs and is returned.
                conn.commit();
                connPool.release(conn);
                
                
                
            } catch (SQLException e) {
                throw new DataException("Could not retrieve record for id=" + id, e);
            } catch (DataException e) {
                throw new DataException("Could not retrieve record for id=" + id, e);
            } catch (ConnectionPoolException e) {
                throw new DataException("Could not retrieve record for id=" + id, e);
            }
            //}//elsenotcache
            // Return object
            return object;
            
        //}
    }

    private double readProductPrice(String id, Connection conn) throws SQLException, DataException {
        double object = 0;
        System.out.println("id is ins saleDAOright before read "+id);
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
//        if(cache.containsKey(id)){
//            object = (SaleBO)cache.get(id);
//        }else{
            // Quieries that database
            if(object == 0){
                
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product WHERE product_id = ?");
                pstmt.setString(1, id);
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                if (rs.next()) {
                    object = rs.getDouble("price");
                    

                    
                }else{
                    throw new DataException("Object was not found in the database.");
                }
                
            }
            
        //}//else contains
        // returns object
        return object;
        
    }
    
}

